#!/usr/bin/perl -w
use strict;
use Getopt::Long;

#88888888888888888888888888888888888888888888888888888888888888888888888888888888

#	Usage

#88888888888888888888888888888888888888888888888888888888888888888888888888888888

my $usage = <<'USAGE';

USAGE:

    panda_create_fasta.pl
                             --temp_dir /local/databases/panda
                             --results /local/results/panda.fasta
                             --db_login "dbname db_user"
                             --pi_list /local/databases/panda/pi.list
                             [--use_panda_ids]
                             [--seq_where "len=36"]
                             [--acc_where "tag='nr'"]
                             [--help]

            --use_panda_ids is an optional flag indicating
                 that Panda IDs should be included on the accession lines
            --temp_dir should be read/write accessible to postgreSQL
            --results
            --xxx_where are optional parameters to filter results
                 using SQL 'WHERE' clauses.
                 In this case, only sequences with exactly 36 amino acids
                 linked to accessions from NR are included.
            --pi_list contains a list of panda numbers in the
                 following format:
                 num1<tab>num2<newline>
                 num1<tab>num2<newline>
RESULTS:

  --results   all matching, fully annotated sequences in panda.


    This script extracts all sequences from panda
       with their all their linked accessions concatenated on one line
       in the fashion of NCBI NR.
    Two intermediate temporary files containing separately only the
       sequences and accessions are produced
       before being stitched together in FASTA format.


USAGE


#88888888888888888888888888888888888888888888888888888888888888888888888888888888

#	Command line options

#88888888888888888888888888888888888888888888888888888888888888888888888888888888
my $TEMP_DIR;
my $DB_LOGIN;
my $pi_list = '';
my $seq_where = '';
my $acc_where = '';
my $help;
my $use_panda_ids;
GetOptions(	'temp_dir=s'	=> \$TEMP_DIR,
			'db_login=s'	=> \$DB_LOGIN,
			'pi_list=s'		=> \$pi_list,
			'results=s'			=> \$results,
			'seq_where=s'	=> \$seq_where,
			'acc_where=s'	=> \$acc_where,
			'use_panda_ids'
							=> \$use_panda_ids,
			'help'			=> \$help);

			
die $usage if ($help);
die "\nERROR:\n\tMissing Argument: db_login\n" .$usage
				unless ($DB_LOGIN);
die "\nERROR:\n\tMissing Argument: temp_dir\n" .$usage
				unless ($TEMP_DIR);
die "\nERROR:\n\tMissing Argument: results\n" .$usage
				unless ($results);
die "\nERROR:\n\tMissing Argument: pi_list\n" .$usage
				unless ($pi_list);




#88888888888888888888888888888888888888888888888888888888888888888888888888888888

#	Create SQL file

#88888888888888888888888888888888888888888888888888888888888888888888888888888888

# pre-pend SQL WHERE to $where string
$seq_where = 'WHERE '.$seq_where unless ($seq_where eq '');
$acc_where = 'WHERE '.$acc_where unless ($acc_where eq '');

# remove trailing slash
$TEMP_DIR =~ s/\/$//;

open (TMP_FILE, ">$TEMP_DIR/tmp.sql") or die "Couldn't open $TEMP_DIR/tmp.sql\n$^E\n";
while (<DATA>)
{
	s/PI_LIST/$pi_list/;
	s/TEMP_DIR/$TEMP_DIR/;
	s/ACC_WHERE/$acc_where/;
	s/SEQ_WHERE/$seq_where/;
	print TMP_FILE;
}


#88888888888888888888888888888888888888888888888888888888888888888888888888888888

#	execute SQL, merge files and cleanup

#88888888888888888888888888888888888888888888888888888888888888888888888888888888

$use_panda_ids = ($use_panda_ids) ? "-p" : "";

system ("psql ".$DB_LOGIN. " -t -F \"\t\" -A -q -f $TEMP_DIR/tmp.sql");
print STDERR "\n***********************************************************************\n";
my ($s, $m, $h) = localtime(time);
printf STDERR "Convert sequences into FASTA format. Start   at %02d:%02d:%02d\n", $h,$m,$s;
system ("panda_merge_accseq $use_panda_ids ".
		"$TEMP_DIR/panda_acc.tmp ".
        "$TEMP_DIR/panda_seq.tmp ".
		"$results");
my ($s, $m, $h) = localtime(time);
printf STDERR "Convert sequences into FASTA format. Finish  at %02d:%02d:%02d\n", $h,$m,$s;
print STDERR "***********************************************************************\n";
system ("rm $TEMP_DIR/tmp.sql");
system ("rm $TEMP_DIR/panda_acc.tmp");
system ("rm $TEMP_DIR/panda_seq.tmp");





#88888888888888888888888888888888888888888888888888888888888888888888888888888888

#	SQL instructions for PSQL follows

#88888888888888888888888888888888888888888888888888888888888888888888888888888888
__DATA__
SELECT '***********************************************************************';
SELECT 'BEGIN  list all nr accessions from Panda at ', current_time;

SELECT 'Save to TEMP_DIR/panda_acc.tmp';

\o 'TEMP_DIR/panda_acc.tmp'
SELECT  pi1,
		pi2,
		acc_text
			FROM accessions
			ACC_WHERE
			ORDER BY pi1, pi2;
\o

SELECT 'FINISH at                                   ', current_time;
SELECT '***********************************************************************';

SELECT '***********************************************************************';
SELECT 'BEGIN  Loading list of panda numbers at     ', current_time;
CREATE TEMP TABLE t_pi (pi1 OID, pi2 OID);
COPY  t_pi from 'PI_LIST';
CREATE UNIQUE INDEX i_pi on t_pi (pi1, pi2);
SELECT 'FINISH Loading ' || count(*) || ' at                      '|| current_time from t_pi;
SELECT '***********************************************************************';


SELECT '***********************************************************************';
SELECT 'BEGIN  list all sequences from Panda at     ', current_time;
SELECT 'Save to TEMP_DIR/panda_seq.tmp';

\o 'TEMP_DIR/panda_seq.tmp'
SELECT
		pi1,
		pi2,
		sequence
			FROM sequences s
			NATURAL JOIN t_pi;
			SEQ_WHERE
			ORDER BY pi1, pi2;
\o

SELECT 'FINISH at                                   ', current_time;
SELECT '***********************************************************************';
